import re
import time
import requests
import uuid
import datetime
from flask import make_response, request, send_from_directory
from flask_restful import abort
from flask_login import current_user, login_user
from funcy import project
from sqlalchemy.orm.exc import NoResultFound
from sqlalchemy.exc import IntegrityError
from funcy import partial
import json
import logging
from flask_mail import Message
from redash.utils.encryption import hly_uname, hly_password
from redash import models, limiter, mail, settings
from redash.permissions import (
    require_default,
    require_admin,
)
from redash.handlers.base import (
    BaseResource,
    require_fields,
    get_object_or_404,
    paginate,
    order_results as _order_results,
)

from redash.authentication.account import (
    invite_link_for_user,
    send_invite_email,
    send_password_reset_email,
    send_verify_email,
)
from redash.settings import parse_boolean
from redash import settings
from redash.serializers import (
    serialize_data_result_to_xlsx,
)
import sys
import random
from typing import List


class EmployeeNewResource(BaseResource):
    def get(self):
        result = models.Employee.get_id()
        return result[0]

    @require_default
    def post(self):
        req = request.get_json(force=True)
        require_fields(req, ("code","name"))
        if not hly_password():
            msg = "请使用正版软件！设备信息：" + hly_uname() + "；许可证：" + settings.REGISTER["common"]
            abort(400, message=msg)
        employee_id=req["id"]
        frontmoney = req["frontmoney"] if len(str(req["frontmoney"]))>0 else 0
        price = req["price"] if len(str(req["price"]))>0 else 0
        informationfee = req["informationfee"] if len(str(req["informationfee"]))>0 else 0
        maintainer = req["maintainer"] if len(str(req["maintainer"]))>0 else 0
        signatory = req["signatory"] if len(str(req["signatory"]))>0 else 0
        socialsecurity = req["socialsecurity"] if "socialsecurity" else 0
        periods = req["periods"] if len(str(req["periods"]))>0 else 0
        signamount = req["signamount"] if len(str(req["signamount"]))>0 else 0
        excelin = req["excelin"] if len(str(req["excelin"]))>0 else 0
        idcard = req["idcard"]
        if len(idcard)>0 :
            idcardcheck = models.Employee.check_employee_field(employee_id,"idcard",idcard)
            if idcardcheck is not None:
                abort(400, message="身份证号已存在:"+idcardcheck[0]+",不能重复.")
        mobile = req["mobile"]
        if len(mobile)>0 :
            mobilecheck = models.Employee.check_employee_field(employee_id,"mobile",mobile)
            if mobilecheck is not None:
                abort(400, message="手机号已存在:"+mobilecheck[0]+",不能重复.")

        code = req["code"]
        codecheck = models.Employee.check_employee_field(employee_id,"code",code)
        if codecheck is not None:
            abort(400, message="人员编号已存在:"+codecheck[0]+",不能重复.")
        bytstatus = req["bytstatus"]
        schooled =  req["schooled"] if "schooled" in req else ""
        if bytstatus=="签约" :
            try:
                message="状态为签约时，必须上传附件。"
                attaches = models.Attaches.get_by_id(employee_id,"employee")
                ps = attaches.to_dict()
                annex = json.loads(json.dumps(ps['annex']))
                nums1=0  #证书(执业资格证或职称证)
                nums2=0  #毕业证
                nums3=0  #身份证(正面)
                nums4=0  #身份证(反面)
                nums5=0  #寸照（不限底色）
                for i in range(len(annex)):
                    if str(annex[i]["types"]) == "1":
                        nums1=nums1+1
                    elif str(annex[i]["types"]) == "2":
                        nums2=nums2+1
                    elif str(annex[i]["types"]) == "3":
                        nums3=nums3+1
                    elif str(annex[i]["types"]) == "4":
                        nums4=nums4+1
                    elif str(annex[i]["types"]) == "5":
                        nums5=nums5+1
                if nums1 ==0 :
                    message="状态为签约时，必须上传附件 证书(执业资格证或职称证)。"
                    abort(400, message="状态为签约时，必须上传附件 证书(执业资格证或职称证)。")
                if nums2 ==0 :
                    message="状态为签约时，必须上传附件 毕业证。"
                    abort(400, message="状态为签约时，必须上传附件 毕业证。")
                if nums3 ==0 :
                    message="状态为签约时，必须上传附件 身份证(正面)。"
                    abort(400, message="状态为签约时，必须上传附件 身份证(正面)。")
                if nums4 ==0 :
                    message="状态为签约时，必须上传附件 身份证(反面)。"
                    abort(400, message="状态为签约时，必须上传附件 身份证(反面)。")
                if nums5 ==0 :
                    message="状态为签约时，必须上传附件 寸照（不限底色）。"
                    abort(400, message="状态为签约时，必须上传附件 寸照（不限底色）。")
            except :
                abort(400, message=message)
        
        employee = models.Employee(
        id=employee_id,
        org=self.current_org,
        bill_date = req["bill_date"],
        code = code,
        name = req["name"]  if "name" in req else "" ,
        sex = req["sex"]  if "sex" in req else "" ,
        mobile = mobile,
        contacts = req["contacts"]  if "contacts" in req else "" ,
        contactsmobile = req["contactsmobile"]  if "contactsmobile" in req else "" ,
        wechat = req["wechat"]  if "wechat" in req else "",
        idcard = idcard,
        major =req["major"] if "major" in req else "",
        schooled =schooled,
        certificate =req["certificate"] if "certificate" in req else "",
        location =req["location"] if "location" in req else "",
        socialsecurity =socialsecurity,
        area = req["area"] if "area" in req else "",
        threeperson = req["threeperson"] if "threeperson" in req else "",
        biddingexit = req["biddingexit"] if "biddingexit" in req else "",
        certificatestatus = req["certificatestatus"] if "certificatestatus" in req else "",
        price = price,
        frontmoney = frontmoney,
        periodunit = req["periodunit"] if "periodunit" in req else "",
        periods = periods,
        informationfee = informationfee,
        message = req["message"] if "message" in req else "",       
        contracttype = req["contracttype"] if "contracttype" in req else "",
        bytstatus = bytstatus,
        signingtime = req["signingtime"] if "signingtime" in req else None,
        expirationtime = req["expirationtime"]  if "expirationtime" in req else None,
        status = req["status"] if "status" in req else 0,
        openseapool = req["openseapool"] if "openseapool" in req else 0,
        openshow = req["openshow"] if "openshow" in req else "0",
        maintainer = maintainer,
        signatory = signatory,
        userid = req["userid"] if "userid" in req else 0,
        orgunitid = req["orgunitid"] if "orgunitid" in req else 0,
        signamount = signamount,
        excelin = excelin,
        contractcode= req["contractcode"] if "contractcode" in req else "",
        accountname= req["accountname"] if "accountname" in req else "",
        accountcode= req["accountcode"] if "accountcode" in req else "",
        accountbank= req["accountbank"] if "accountbank" in req else "",
        )
        try:
            models.db.session.add(employee)
            models.db.session.commit()
        except IntegrityError as e:
            if "code" in str(e):
                abort(400, message="编号已存在.")
            abort(500)


class EmployeeResource(BaseResource):
    def get(self, employee_id):
        result = models.Employee.get_by_id(employee_id)
        return result.to_dict()

    @require_default
    def post(self, employee_id):
        req = request.get_json(force=True)
        require_fields(req, ("code","name"))
        if not hly_password():
            msg = "请使用正版软件！设备信息：" + hly_uname() + "；许可证：" + settings.REGISTER["common"]
            abort(400, message=msg)
        frontmoney = req["frontmoney"] if len(str(req["frontmoney"]))>0 else 0
        price = req["price"] if len(str(req["price"]))>0 else 0
        informationfee = req["informationfee"] if len(str(req["informationfee"]))>0 else 0
        maintainer = req["maintainer"] if len(str(req["maintainer"]))>0 else 0
        signatory = req["signatory"] if len(str(req["signatory"]))>0 else 0
        socialsecurity = req["socialsecurity"] if len(str(req["socialsecurity"]))>0 else 0
        periods = req["periods"] if len(str(req["periods"]))>0 else 0
        signamount = req["signamount"] if len(str(req["signamount"]))>0 else 0
        excelin = req["excelin"] if len(str(req["excelin"]))>0 else 0
        idcard = req["idcard"]
        if len(idcard)>0 :
            idcardcheck = models.Employee.check_employee_field(employee_id,"idcard",idcard)
            if idcardcheck is not None:
                abort(400, message="身份证号已存在:"+idcardcheck[0]+",不能重复.")
        mobile = req["mobile"]
        if len(mobile)>0 :
            mobilecheck = models.Employee.check_employee_field(employee_id,"mobile",mobile)
            if mobilecheck is not None:
                abort(400, message="手机号已存在:"+mobilecheck[0]+",不能重复.")
        code = req["code"]
        codecheck = models.Employee.check_employee_field(employee_id,"code",code)
        if codecheck is not None:
            abort(400, message="人员编号已存在:"+codecheck[0]+",不能重复.")
        bytstatus = req["bytstatus"]
        schooled =  req["schooled"] if "schooled" in req else ""
        if bytstatus=="签约" :
            try:
                message="状态为签约时，必须上传附件。"
                attaches = models.Attaches.get_by_id(employee_id,"employee")
                ps = attaches.to_dict()
                annex = json.loads(json.dumps(ps['annex']))
                nums1=0  #证书(执业资格证或职称证)
                nums2=0  #毕业证
                nums3=0  #身份证(正面)
                nums4=0  #身份证(反面)
                nums5=0  #寸照（不限底色）
                for i in range(len(annex)):
                    if str(annex[i]["types"]) == "1":
                        nums1=nums1+1
                    elif str(annex[i]["types"]) == "2":
                        nums2=nums2+1
                    elif str(annex[i]["types"]) == "3":
                        nums3=nums3+1
                    elif str(annex[i]["types"]) == "4":
                        nums4=nums4+1
                    elif str(annex[i]["types"]) == "5":
                        nums5=nums5+1
                if nums1 ==0 :
                    message="状态为签约时，必须上传附件 证书(执业资格证或职称证)。"
                    abort(400, message="状态为签约时，必须上传附件 证书(执业资格证或职称证)。")
                if nums2 ==0 :
                    message="状态为签约时，必须上传附件 毕业证。"
                    abort(400, message="状态为签约时，必须上传附件 毕业证。")
                if nums3 ==0 :
                    message="状态为签约时，必须上传附件 身份证(正面)。"
                    abort(400, message="状态为签约时，必须上传附件 身份证(正面)。")
                if nums4 ==0 :
                    message="状态为签约时，必须上传附件 身份证(反面)。"
                    abort(400, message="状态为签约时，必须上传附件 身份证(反面)。")
                if nums5 ==0 :
                    message="状态为签约时，必须上传附件 寸照（不限底色）。"
                    abort(400, message="状态为签约时，必须上传附件 寸照（不限底色）。")
            except :
                abort(400, message=message)
        employee = models.Employee.get_by_id(employee_id)
        params = dict(bill_date=req["bill_date"], code=code,name=req["name"]  if "name" in req else "",
            sex = req["sex"]  if "sex" in req else "",mobile = mobile,idcard = idcard,major =req["major"] if "major" in req else "",
            certificate =req["certificate"] if "certificate" in req else "",threeperson = req["threeperson"] if "threeperson" in req else "",
            location =req["location"]  if "location" in req else "",socialsecurity =socialsecurity,area = req["area"] if "area" in req else "",
            biddingexit = req["biddingexit"]  if "biddingexit" in req else "",price = price,frontmoney = frontmoney,
            periodunit = req["periodunit"]  if "periodunit" in req else "",contracttype = req["contracttype"] if "contracttype" in req else "",
            periods = periods,informationfee = informationfee,message = req["message"]  if "message" in req else "",
            bytstatus = bytstatus,signingtime = req["signingtime"] if "signingtime" in req else None,
            expirationtime = req["expirationtime"] if "expirationtime" in req else None,status = req["status"],
            orgunitid = req["orgunitid"] if "orgunitid" in req else 0,signamount = signamount,excelin = excelin,
            contractcode= req["contractcode"] if "contractcode" in req else "",
            contacts = req["contacts"]  if "contacts" in req else "" ,contactsmobile = req["contactsmobile"]  if "contactsmobile" in req else "" ,
            schooled =schooled,certificatestatus = req["certificatestatus"] if "certificatestatus" in req else "",wechat = req["wechat"] if "wechat" in req else "",
            accountname= req["accountname"] if "accountname" in req else "",accountcode= req["accountcode"] if "accountcode" in req else "",
            accountbank= req["accountbank"] if "accountbank" in req else "",openshow = req["openshow"] if "openshow" in req else "0",
            maintainer = maintainer,signatory = signatory)
        try:
            self.update_model(employee, params)
            models.db.session.commit()
        except IntegrityError as e:
            if "code" in str(e):
                abort(400, message="编号已存在.")
            abort(500)

class EmployeedeleteResource(BaseResource):
    @require_default
    def post(self, employee_id):
        strsql="select count(*) as nums From public.projectdetail where employeeid="+str(employee_id)
        isdelheck =models.enums.Enums.get_selsql(strsql)
        if isdelheck[0] >0:
            abort(400, message="人员已有过挂靠项目，不能删除.")
        try:
            employee =  models.Employee.get_by_id(employee_id)
            strsql="""delete From  attaches where sourcetype='employee' and org_id="""+str(self.current_org.id)+""" and sourceid="""+ employee_id+"""; 
                delete From employeeactivity where sourcetype='employee' and employeeid="""+ employee_id +""" ; """
            models.db.session.execute(strsql)
            models.db.session.delete(employee)
            models.db.session.commit()
        except IntegrityError as e:
            abort(400, message="人员不存在.")

class EmployeeBatchMoveResource(BaseResource):
    @require_default
    def post(self):
        req = request.get_json(force=True)
        userid = str(req["userid"])
        ids = req["ids"]
        sourcetype = str(req["sourcetype"]) if "sourcetype" in req else "0"
        maintainer =str(req["maintainer"]) if "maintainer" in req else "0"
        openseapool = str(req["openseapool"]) if "openseapool" in req else "0"
        message = req["message"] if "message" in req else ""

        if sourcetype =="0" :#普通人员需要审批
            now =time.strftime("%Y-%m-%d")
            strsql=""
            for id in ids :
                strsql=strsql+"""select public.workflowcheck('employee',"""+str(id)+""","""+openseapool+""",1,'批量转移 """+message+"""',"""+userid+""",'"""+now+"""',"""+str(self.current_user.orgunit_id)+""","""+str(self.current_org.id)+""") ; """
            
            employeeids=",".join('%s' %id for id in ids)
            strsql = strsql+ """ update public.employee set maintainermove="""+  maintainer
            strsql = strsql+ """ where id in ( """+employeeids+""");"""
            try:
                models.db.session.execute(strsql)
                models.db.session.commit()
                return {"code": "sucess","message":"操作成功。"}
            except :
                return {"code": "err","message":"操作失败。"}
        else :
            employeeids=",".join('%s' %id for id in ids)
            if maintainer !="0" :
                openseapool="1"

            strsql =""" update public.employee set status=0"""
            if openseapool=="0" :
                strsql =strsql+""" ,openseapool = 1,maintainer=0 """
            else :
                strsql =strsql+""" ,openseapool = 0,maintainer="""+  maintainer

            strsql =strsql+""" where id in ( """+employeeids+""")"""

            strsqlactivity ="""insert into public.employeeactivity(userid,type,employeeid,message,sourcetype)
            select """+userid+""",1,id,'批量转移 """+message+"""','employee'
            From public.employee  where id in ( """+employeeids+""")"""
            try:
                models.db.session.execute(strsql)
                models.db.session.execute(strsqlactivity)
                models.db.session.commit()
                return {"code": "sucess","message":"操作成功。"}
            except :
                return {"code": "err","message":"操作失败。"}

class EmployeeListResource(BaseResource):
    def post(self):
        req = request.get_json(force=True)
        page = req["page"]
        page_size = req["pagesize"]
        code = req["code"] if "code" in req else ""
        name = req["name"] if "name" in req else ""
        major = req["major"] if "major" in req else ""
        area = req["area"] if "area" in req else ""
        userid = str(req["userid"])if "userid" in req else ""
        bytstatus= req["bytstatus"] if "bytstatus" in req else ""
        certificate = req["certificate"] if "certificate" in req else ""
        location = req["location"] if "location" in req else ""
        mobile = req["mobile"] if "mobile" in req else ""
        contacts = req["contacts"]  if "contacts" in req else "" 
        contactsmobile = req["contactsmobile"]  if "contactsmobile" in req else "" 
        idcard = req["idcard"] if "idcard" in req else ""
        status = str(req["status"]) if "status" in req else ""
        openseapool = str(req["openseapool"]) if "openseapool" in req else ""
        periodunit = str(req["periodunit"]) if "periodunit" in req else ""
        maintainer =str(req["maintainer"]) if "maintainer" in req else ""
        datadown = req["datadown"] if "datadown" in req else False
        genjinbegindate= req["genjinbegindate"] if "genjinbegindate" in req else ""
        genjinenddate= req["genjinenddate"] if "genjinenddate" in req else ""
        orderfield = req["orderfield"] if "orderfield" in req else "code"
        projectsel = str(req["projectsel"]) if "projectsel" in req else "0" #是否选单
        order = req["order"] if "order" in req else 0
        if orderfield=="code" :
            orderfield ="t1.code"
        elif orderfield=="name":
            orderfield ="t1.name"
        elif orderfield=="major":
            orderfield ="t1.major"
        elif orderfield=="area":
            orderfield ="t1.area"
        elif orderfield=="bill_dateformat":
            orderfield ="t1.bill_date"
        elif orderfield=="genjindate":
            orderfield ="t8.genjindate"      
        else:
            orderfield ="t8.genjindate"        

        strwhere ="t1.org_id=" +str(self.current_org.id)
        if code !="" :
            strwhere = strwhere + " and t1.code like '%"+code+"%'"
        if name !='' :
            strwhere = strwhere + " and t1.name like '%"+name+"%'"
        if major !='' :
            strwhere = strwhere + " and  '"+major+"' = ANY(t1.major)"
        if area !='' :
            strwhere = strwhere + " and t1.area like '%"+area+"%'"
        if bytstatus !='' :
            strwhere = strwhere + " and t1.bytstatus like '%"+bytstatus+"%'"
        if certificate !='' :
            strwhere = strwhere + " and array_to_string(t1.certificate,',') like '%"+certificate+"%' "
        if location !='' :
            strwhere = strwhere + " and t1.location like '%"+location+"%'"
        if mobile !='' :
            strwhere = strwhere + " and t1.mobile like '%"+mobile+"%'"
        if contacts !='' :
            strwhere = strwhere + " and t1.contacts like '%"+contacts+"%'"
        if contactsmobile !='' :
            strwhere = strwhere + " and t1.contactsmobile like '%"+contactsmobile+"%'"
        if idcard !='' :
            strwhere = strwhere + " and t1.idcard like '%"+idcard+"%'"
        if status !='' :
            strwhere = strwhere + " and t1.status = '"+status+"'"
        if openseapool !='' :
            strwhere = strwhere + " and t1.openseapool = '"+openseapool+"'"
        if maintainer !='' and openseapool !='1' :
            strwhere = strwhere + " and t1.maintainer = '"+maintainer+"'"
        if periodunit !='' :
            strwhere = strwhere + " and t1.periodunit = '"+periodunit+"'"
        if projectsel == "1":
            strwhere = strwhere + """ and t1.id not in (select t2.employeeid From project t1 inner join projectdetail t2 on t1.id=t2.projectid)
            and t1.openshow=1 """
        if genjinbegindate !='' :
            strwhere = strwhere + " and t8.genjindate >= '"+genjinbegindate+"'"
        if genjinenddate !='' :
            strwhere = strwhere + " and to_char(t8.genjindate,'YYYY-MM-DD') <= '"+genjinenddate+"'"

        if userid !="":
            strwhere = strwhere + " and t1.userid = '"+userid+"'"

        strgetfield =""" t1.id,to_char(t1.bill_date,'YYYY-MM-DD') bill_dateformat,t1.code,t1.sex,t1.wechat,t1.contacts,t1.contactsmobile,
        substring(t1.name,1,1) || substring('***',1,length(t1.name)-1) as name,"""
        if openseapool=="0" :
            strgetfield =strgetfield + """t1.mobile as mobilex, t1.idcard as idcardx,"""
        else :
            strgetfield =strgetfield + """ '*******' || substring(t1.mobile,8) as mobilex, 
                '**************' || substring(t1.idcard,15) as idcardx,"""

        strgetfield =strgetfield +""" array_to_string(t1.major,',') major,t1.schooled,array_to_string(t1.certificate,',') certificate,t1.location,
                case when t1.socialsecurity=1 then '有' else '无' end socialsecuritystr,t1.status,
                t1.area,t1.threeperson,t1.biddingexit,t1.certificatestatus,t1.price,t1.frontmoney,t1.periodunit,t1.periods,t1.informationfee,
                t1.message,t1.contracttype,t1.bytstatus,COALESCE(to_char(t1.signingtime,'YYYY-MM-DD'),'') signingtimeformat,
                COALESCE(to_char(t1.expirationtime,'YYYY-MM-DD'),'') expirationtimeformat,
                case when t1.status=0 then '草稿' when t1.status=1 then '审核中' else '已审核' end as strstatus,t1.openseapool,
                case when t1.openseapool=0 then '私有池' else '公海池' end stropenseapool,
                t1.maintainer,t1.signatory,t1.userid,t1.updated_at,t1.created_at,t1.signamount,t1.contractcode,
                case when t1.excelin=0 then '手工录入' else 'EXCEL导入' end as strexcelin,
                COALESCE(t2.name,'') as username,COALESCE(t3.name,'') as maintainername,COALESCE(t4.name,'')  as signatoryname,
                COALESCE(t5.name,'') as orgunitname,
                COALESCE(t6.amount,0) as amountout,COALESCE(t6.othertype,'') as othertypeout,COALESCE(t6.otheramount,0) as otheramountout,
                COALESCE(to_char(t6.paydate,'YYYY-MM-DD'),'') paydateformatout,
                COALESCE(t7.amount,0) as amountin,COALESCE(to_char(t7.paydate,'YYYY-MM-DD'),'') paydateformatin,
                COALESCE(to_char(t8.genjindate,'YYYY-MM-DD'),'') as genjindate """

        tblname = """public.employee t1 left join public.users t2 on t1.userid=t2.id
                left join public.users t3 on t1.maintainer=t3.id
                left join public.users t4 on t1.signatory=t4.id
                left join public.orgunit t5 on t1.orgunitid=t5.id
                left join (select sum(t2.amount) as amount,max(t2.othertype) as othertype,sum(t2.otheramount) as otheramount,t2.employeeid,t2.contractcode,
                max(t1.paydate) paydate
                From public.payment t1 inner join public.paymentactivity t2 on t1.id=t2.paymentid
                where t1.type=0 group by t2.employeeid,t2.contractcode) t6 on t1.id=t6.employeeid and t1.contractcode=t6.contractcode
                left join (select sum(t2.amount) as amount,t2.employeeid,t2.contractcode,
                max(t1.paydate) paydate
                From public.payment t1 inner join public.paymentactivity t2 on t1.id=t2.paymentid
                where t1.type=1 group by t2.employeeid,t2.contractcode) t7 on t1.id=t7.employeeid and t1.contractcode=t7.contractcode
                left join (select employeeid,max(updated_at) as genjindate From public.employeeactivity where type=0
                group by employeeid) t8 on t1.id=t8.employeeid """
        #orderfield ="t1.id"
        if not datadown :
            result = models.Enums.get_pagequery(tblname,strgetfield,orderfield,page_size,page,order,strwhere)
            return result[0]
        else :
            showfieldparam="employeelist1"
            if openseapool =='1' :
                showfieldparam="employeelist2"
            queryshowfield = models.QueryShowField.get_by_code(showfieldparam)
            ps = queryshowfield.to_dict()
            fieldsshow = ps['fieldsshow']
            fieldsall = json.loads(json.dumps(ps['fieldsall'])) 
            columns =[]
            strsql="select " + strgetfield + " from " + tblname +" where " +strwhere
            strgetfield = ""
            for i in range(len(fieldsall)):
                if fieldsall[i]["key"] in fieldsshow:
                    columns.append({ 'name' : fieldsall[i]["title"]})
                    strgetfield = strgetfield +","+  fieldsall[i]["key"] +" as "+ fieldsall[i]["title"]
                    
            orderfield = req["orderfield"] if "orderfield" in req else "code"
            strsql = "select " + strgetfield[1:] + " from (" + strsql + ") t1 order by "+orderfield
            if order==0 :
                strsql= strsql+" ASC "
            else :
                strsql= strsql+" DESC "
            strsql = """select array_to_json(array_agg(row_to_json(t))) from (""" + strsql + """) t """
            data = models.Enums.get_selsql(strsql)

            # data2 = json.loads(json.dumps(article_info))
            # data2["code"]="code";
            # data2["name"]="name";
            # image_dict2 = data2
            # image_aug2 = [] # 先创建一个列表
            # image_aug2.append(image_dict2) # 依据列表的append对文件进行追加
            # data2["code"]="code1";
            # data2["name"]="name1";
            # image_dict2 = data2
            # image_aug2.append(image_dict2) # 依据列表的append对文件进行追加
            article_info = {}
            da1 = json.loads(json.dumps(article_info))
            ddd= data[0]
            da1['columns'] = columns #[{"name": "code", "friendly_name": "code", "type": null}, {"name": "name", "friendly_name": "name", "type": null}]
            if data[0] is None:
                da1['rows'] = data[0]
            da1['rows'] = data[0] #[{"code": "code", "name": "name"}, {"code": "code1", "name": "name1"}]

            query_result = da1

            result = serialize_data_result_to_xlsx(query_result,"renyuan")
            return send_from_directory(result, "renyuan.xlsx",as_attachment=True)

#     选单
class EmployeeSelListResource(BaseResource):
    def post(self):
        req = request.get_json(force=True)
        bytstatus = req["bytstatus"]
        billid = str(req["id"])  if "id" in req else "0"

        strsql ="""select id,code,name,idcard,price,frontmoney,periodunit,periods,informationfee,signingtime,expirationtime,
            maintainer,signatory,userid From public.employee 
            where org_id=""" +str(self.current_org.id)
        if bytstatus !='' :
            strsql = strsql+ """ and (bytstatus='"""+bytstatus+"""') or id in(select employeeid from public.payment where id="""+billid+""")"""

        strsql = """select array_to_json(array_agg(row_to_json(t))) from (""" + strsql + """) t """
        result = models.Enums.get_selsql(strsql)
        return result[0]